package com.hava.spring_data.repository;

import com.hava.spring_data.entity.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
import java.util.Map;

/**
 * Created by yanfa on 2016/10/24.
 */
@Repository
public class MyJdbcTemplateDao {

    private JdbcTemplate jdbcTemplate;

    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Autowired
    public void setDataSource(DataSource dataSource){
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    }

    public void createTable()
    {
        System.out.println("ceateTable");
        String sql = "CREATE TABLE user (id INTEGER,first_name VARCHAR(100), last_name VARCHAR(100))";

        jdbcTemplate.execute(sql);
    }

    public void insertData(){
        this.jdbcTemplate.update("INSERT INTO user VALUES (1, ?, ?)", "Meimie","Han");
        this.jdbcTemplate.update("INSERT INTO user VALUES (1, ?, ?)", "Lei","Li");
    }

    public int count(){
        String sql = "SELECT COUNT(*) FROM user";
        return this.jdbcTemplate.queryForObject(sql,Integer.class);
    }

    public List<User> getUserList(){
        return this.jdbcTemplate.query("SELECT * FROM user", new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet resultSet, int rowNumber) throws SQLException {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setFirst_name(resultSet.getString("first_name"));
                user.setLast_name(resultSet.getString("last_name"));
                return user;
            }
        });
    }

    public int countOfUsersByFirstName(String firstName){
        String sql = "SELECT COUNT(*) FROM user WHERE first_name = :firstName;";

        Map<String ,String> nameParameters = Collections.singletonMap("firstName",firstName);

        return this.namedParameterJdbcTemplate.queryForObject(sql,nameParameters,Integer.class);
    }

    public int countOfUsersByFirstName(User user){
        String sql = "SELECT COUNT(*) FROM user WHERE first_name = :first_name;";

        SqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(user);

        return this.namedParameterJdbcTemplate.queryForObject(sql,sqlParameterSource,Integer.class);
    }
}
